import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class excelmerge {
/*
    public static class ThreeSheet {
        public String key;
        public String[] vec;

        ThreeSheet() {
            key = new String();
            vec = new String[7 + 7 + 7 + 7];
        }

        @Override
        public boolean equals(Object obj) {
            return this.key.equals(((ThreeSheet) obj).key);
        }

        @Override
        public int hashCode() {
            return key.hashCode();
        }
    }
*/

    public static void main(String[] args) {
        try {
            showExcel2("C:\\Users\\ch\\Desktop\\债券账户梳理情况汇总表.xlsx");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void showExcel2(String path) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(path)));
        XSSFSheet sheet = null, sheet1, sheet2;
        ArrayList<XSSFRow> allindexeditems = new ArrayList<XSSFRow>();
        //indexes
        sheet1 = workbook.getSheetAt(1);
        sheet2 = workbook.getSheetAt(2);
        HashMap<String, XSSFRow> index1 = new HashMap<String, XSSFRow>();
        for (int j = 1; j < sheet1.getPhysicalNumberOfRows(); j++) {
            XSSFRow tmp1 = sheet1.getRow(j);
            String skey = tmp1.getCell(1).toString().trim();
            String value = tmp1.getCell(5).toString();
            if (value.contains("-")) {
                String[] ss1 = value.split("-");
                value = ss1[2] + "年" + ss1[1] + ss1[0] + "日";
            } else if (value.contains("/")) {
                String[] sss = value.split("/");
                value = sss[2] + "年" + sss[0] + "月" + sss[1] + "日";
            } else if (value.length() == 8) {
                String tmp = value.substring(0, 4) + "年" + value.substring(4, 6) + "月" + value.substring(6, 8) + "日";
                value = tmp;
            }
            tmp1.getCell(5).setCellValue(value);
            index1.put(skey, tmp1);
        }
        HashMap<String, XSSFRow> index2 = new HashMap<String, XSSFRow>();
        for (int j = 1; j < sheet2.getPhysicalNumberOfRows(); j++) {
            XSSFRow tmp2 = sheet2.getRow(j);
            String skey = tmp2.getCell(1).toString().trim();
            String value = tmp2.getCell(5).toString();
            if (value.contains("-")) {
                String[] ss1 = value.split("-");
                value = ss1[2] + "年" + ss1[1] + ss1[0] + "日";
            } else if (value.contains("/")) {
                String[] sss = value.split("/");
                value = sss[2] + "年" + sss[0] + "月" + sss[1] + "日";
            } else if (value.length() == 8) {
                String tmp = value.substring(0, 4) + "年" + value.substring(4, 6) + "月" + value.substring(6, 8) + "日";
                value = tmp;
            }
            tmp2.getCell(5).setCellValue(value);
            index2.put(skey, tmp2);
        }
        //do
        ArrayList<XSSFRow> al = new ArrayList<XSSFRow>();
        sheet = workbook.getSheetAt(0);
        XSSFRow nullrow = sheet.getRow(0);
        nullrow.createCell(0).setCellValue(" ");
        ArrayList<XSSFRow> bak = new ArrayList<XSSFRow>();
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
            XSSFRow row = sheet.getRow(j);
            if (row == null || j == 0)
                continue;
            if (row.getCell(5) != null) {
                String value = row.getCell(5).toString();
                if (value.contains("-")) {
                    String[] ss1 = value.split("-");
                    value = ss1[2] + "年" + ss1[1] + ss1[0] + "日";
                } else if (value.contains("/")) {
                    String[] sss = value.split("/");
                    value = sss[2] + "年" + sss[0] + "月" + sss[1] + "日";
                } else if (value.length() == 8) {
                    String tmp = value.substring(0, 4) + "年" + value.substring(4, 6) + "月" + value.substring(6, 8) + "日";
                    value = tmp;
                }
                row.getCell(5).setCellValue(value);
            }
            bak.add(row);
        }
        String oldname = "";
        for (int i = 0; i < bak.size(); i++) {
            String flag = bak.get(i).getCell(0).toString();
            if (flag != "") {
                oldname = flag;
            } else {
                bak.get(i).getCell(0).setCellValue(oldname);
            }
        }
        oldname = "";
        for (int i = 0; i < bak.size(); i++) {
            System.out.println("index: " + i);
            String flag = bak.get(i).getCell(0).toString();
            if (i == 0) {
                oldname = flag;
                al.add(bak.get(i));
                continue;
            }
            if (flag.equals(oldname)) {
                oldname = flag;
                al.add(bak.get(i));
            } else {
                oldname = flag;
                if (al.size() <= 0)
                    continue;
                //sort
                if (al.size() == 2) {
                    String s1 = al.get(0).getCell(2).toString();
                    String s2 = al.get(1).getCell(2).toString();
                    String ss = s1.length() > s2.length() ? s1 : s2;
                    al.get(0).getCell(2).setCellValue(ss);
                    al.get(1).getCell(2).setCellValue(ss);
                    //
                    int l1 = al.get(0).getCell(1).toString().length();
                    int l2 = al.get(1).getCell(1).toString().length();
                    if (l1 <= l2) {
                        XSSFRow r = al.get(0);
                        al.remove(0);
                        al.add(r);
                    }
                }
                for (XSSFRow tmprow : al) {
                    allindexeditems.add(tmprow);
                }
                if(al.size()==1){
                    allindexeditems.add(nullrow);
                }
                //search
                for (XSSFRow tmprow : al) {
                    String tmpkey = tmprow.getCell(1).toString().trim();
                    if (index1.containsKey(tmpkey)) {
                        allindexeditems.add(index1.get(tmpkey));
                    } else if (index2.containsKey(tmpkey)) {
                        allindexeditems.add(index2.get(tmpkey));
                    } else {
                        allindexeditems.add(nullrow);
                    }
                }
                if(al.size()==1){
                    allindexeditems.add(nullrow);
                }
                al.clear();
                al.add(bak.get(i));
            }
        }
        // write out
        Map<String, List<List<ExcelData>>> outdata = new LinkedHashMap<String, List<List<ExcelData>>>();
        List<List<ExcelData>> outsheet = new ArrayList<List<ExcelData>>();
        List<ExcelData> linedata = null;
        int BC = 1;
        //
        ArrayList<XSSFRow> tmpalblock = new ArrayList<XSSFRow>();
        for (int i = 0; i < allindexeditems.size(); i += 2) {
            System.out.println("write:" + i);
            XSSFRow r1 = allindexeditems.get(i);
            XSSFRow r2 = allindexeditems.get(i + 1);
            tmpalblock.add(r1);
            tmpalblock.add(r2);
//            if (r1.getCell(0).toString().equals(r2.getCell(0).toString())) {
//                //write
//                if (i + 3 <= allindexeditems.size()) {
//                    tmpalblock.add(allindexeditems.get(i + 2));
//                    tmpalblock.add(allindexeditems.get(i + 3));
//                }
//                i += 2;
//            }else{
//                tmpalblock.add(nullrow);
//                tmpalblock.add(nullrow);
//            }
            if (i + 3 <= allindexeditems.size()) {
                tmpalblock.add(allindexeditems.get(i + 2));
                tmpalblock.add(allindexeditems.get(i + 3));
            }
            i += 2;
            for (int j = 0; j < tmpalblock.size(); j++) {
                linedata = new ArrayList<ExcelData>();
                XSSFRow row = tmpalblock.get(j);
                ExcelData excelData = null;
                switch (j) {
                    case 0:
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("" + BC++);
                        linedata.add(excelData);
                        //
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("同业拆借中心");
                        linedata.add(excelData);
                        break;
                    case 1:
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("");
                        linedata.add(excelData);
                        //
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("同业拆借中心");
                        linedata.add(excelData);
                        break;
                    case 2:
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("");
                        linedata.add(excelData);
                        //
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("中央结算公司");
                        linedata.add(excelData);
                        break;
                    case 3:
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("");
                        linedata.add(excelData);
                        //
                        excelData = new ExcelData();
                        excelData.setColSpan(1);
                        excelData.setRowSpan(1);
                        excelData.setValue("上海清算所");
                        linedata.add(excelData);
                        break;
                }
                for (int k = 0; k < 8; k++) {
                    String value = row.getCell(k) == null ? "" : row.getCell(k).toString();
                    excelData = new ExcelData();
                    excelData.setColSpan(1);
                    excelData.setRowSpan(1);
                    excelData.setValue(value);
                    linedata.add(excelData);
                }
                outsheet.add(linedata);
            }
            tmpalblock.clear();
        }
        //收尾工作
        try {
            outdata.put("表1", outsheet);
            String outpath = "C:\\Users\\ch\\Desktop\\债券账户梳理情况汇总表-result.xlsx";
            System.out.println("write:" + outpath);
            Excel_reader.testWrite(new FileOutputStream(new File(outpath)), "xlsx", outdata);
        } catch (IOException e) {
            e.getStackTrace();
        }
    }
}
